package org.dhbw.oodb.dmlp.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import oracle.jdbc.OraclePreparedStatement;

import org.dhbw.oodb.dmlp.entity.Customer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;


@Component("customerDAO")
public class CustomerDaoImpl implements CustomerDao{

	@SuppressWarnings("unused")
	public DataSource dataSource;
	
	private SimpleJdbcTemplate insertTemplate;
	
	@Autowired
	public void setDataSource (@Qualifier("dataSource")DataSource dataSource) {
		this.dataSource = dataSource;
		//this.jdbcInsert = new SimpleJdbcInsert(dataSource).withTableName("customer"); //No autoeygeneration used here
		this.insertTemplate = new SimpleJdbcTemplate(dataSource);
	}
	
	@Transactional("SPRINGTX")
	public Customer insert(Customer object) {
		String insert = "Insert into Customer (c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment) values(:c_custkey, :c_name, :c_address, :c_nationkey, :c_phone, :c_acctbal, :c_mktsegment, :c_comment)";
		this.insertTemplate.update(insert, new BeanPropertySqlParameterSource(object));
		return null;
	}
	
	@Transactional("SPRINGTX")
	public void insert(ArrayList<Customer> objects) {
		for(Customer cust : objects){
			String insert = "Insert into Customer (c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment) values(:c_custkey, :c_name, :c_address, :c_nationkey, :c_phone, :c_acctbal, :c_mktsegment, :c_comment)";
			this.insertTemplate.update(insert, new BeanPropertySqlParameterSource(cust));
		}
	}
	
	@Transactional("SPRINGTX")
	public void batchInsert(ArrayList<Customer> objects) throws SQLException{
		/*Statement statement = this.dataSource.getConnection().createStatement();
		
		for(Customer cust : objects){
			String insert = "Insert into Customer (c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment) values("+
			cust.getC_custkey()+", '"+
			cust.getC_name()+"', '"+
			cust.getC_address()+"', "+
			cust.getC_nationkey()+", '"+
			cust.getC_phone()+"', "+
			cust.getC_acctbal()+", '"+
			cust.getC_mktsegment()+"', '"+
			cust.getC_comment()+"')";
			
			statement.addBatch(insert);
		}
		
		statement.executeBatch();
		statement.close();
		this.dataSource.getConnection().commit();*/
		
		SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(objects.toArray());
		String sql = "Insert into Customer (c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment) values(:c_custkey, :c_name, :c_address, :c_nationkey, :c_phone, :c_acctbal, :c_mktsegment, :c_comment)";
		
		this.insertTemplate.batchUpdate(sql, batch);		

	}
	
	@Transactional("SPRINGTX")
	public void batchInsertPrepared(final ArrayList<Customer> objects) throws SQLException{
		
		String sql = "Insert into Customer (c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment) values(?,?,?,?,?,?,?,?)";
		this.insertTemplate.getJdbcOperations().batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setLong(1, objects.get(i).getC_custkey());
				ps.setString(2, objects.get(i).getC_name());
				ps.setString(3, objects.get(i).getC_address());
				ps.setInt(4, objects.get(i).getC_nationkey());
				ps.setString(5, objects.get(i).getC_phone());
				ps.setDouble(6, objects.get(i).getC_acctbal());
				ps.setString(7, objects.get(i).getC_mktsegment());
				ps.setString(8, objects.get(i).getC_comment());				
			}
			
			public int getBatchSize() {
				return objects.size();
			}
		});
	}
	
	public void truncate(){
		this.insertTemplate.getJdbcOperations().execute("Truncate Table Customer");
	}

}
